import shutil

import openpyxl
import datetime

import os
import win32ui
import win32con
import time
from openpyxl.utils import get_column_letter
from tqdm import tqdm
from tools_pmc import *


def vlookup_1(lookup_value, table_array):
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            return [row[13].value,  # 可用数量
                    row[26].value  # 入库数量
                    ]


def vlookup(lookup_value, table_array, list1: list):
    list2 = []
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            for i in list1:
                list2.append(row[i - 1].value)
            return list2


#
# def qyd(sn, list_col: list):
#     list_temp = []
#     for i_y in range(2, ws_mccr_yestoday.max_row + 1):
#         if ws_mccr_yestoday.cell(i_y, 1).value == sn:
#             for m in list_col:
#                 list_temp.append(ws_mccr_yestoday.cell(i_y, m).value)
#             break
#     return list_temp
#
#
# def pam_matrix(i_sn, list_title: list, list_cols: list, list_tav: list):
#     str1 = ''
#     for n in range(len(list_title)):
#         if ws_mccr_today.cell(i_sn, list_cols[n]).value == list_tav[n]:
#             str1 += list_title[n] + "=" + str(ws_mccr_today.cell(i_sn, list_cols[n]).value) + "\n"
#         else:
#             str1 += list_title[n] + "=" + str(
#                 ws_mccr_today.cell(i_sn, list_cols[n]).value) + "(标准值=" + str(list_tav[0]) + ")" + "\n"
#     return str1
#
#
# def pam(i_sn, saterial_status):
#     list_temp1 = qyd(ws_mccr_today.cell(i_sn, 1).value, [10, 11, 12, 13, 6, 19, 21])
#     str1 = "关键参数监控：\n"
#     if saterial_status in ["未启用"]:
#         str1 += pam_matrix(i_sn,
#                            ["可用量", "计划请购量", "非计划请购量", "BOM成品引用次数"],
#                            [6, 10, 11, 19],
#                            [0, list_temp1[0], list_temp1[1], 0])
#
#     elif saterial_status == "有呆滞风险":
#         str1 += pam_matrix(i_sn,
#                            ["计划请购量", "非计划请购量", "进货数量", "BOM成品引用次数"],
#                            [10, 11, 13, 19],
#                            [list_temp1[0], list_temp1[1], list_temp1[3], 0])
#     elif saterial_status in ["未销货呆滞", "未启用呆滞"]:
#         str1 += pam_matrix(i_sn,
#                            ["可用量", "计划请购量", "非计划请购量", "BOM成品引用次数"],
#                            [6, 10, 11, 19],
#                            [list_temp1[4], list_temp1[0], list_temp1[1], 0])
#     elif saterial_status in ["停用呆滞", "停用"]:
#         str1 += pam_matrix(i_sn,
#                            ["可用量", "计划请购量", "非计划请购量", "非原材料仓请购量", "进货数量",
#                             "Y状态BOM成品引用次数"],
#                            [6, 10, 11, 12, 13, 21],
#                            [list_temp1[4], list_temp1[0], list_temp1[1], list_temp1[2], list_temp1[3], 0])
#
#     elif saterial_status == "正常A":
#         if ws_mccr_today.cell(i_sn, 21).value == 0:
#             str1 += "Y状态BOM成品引用次数=0，标准值≠0\n"
#         else:
#             str1 += "Y状态BOM成品引用次数≠0\n"
#         if ws_mccr_today.cell(i_sn, 30).value < 500:
#             str1 += "365天生产使用量=" + str(ws_mccr_today.cell(i_sn, 30).value) + "标准值>500\n"
#         else:
#             str1 += "365天生产使用量>500"
#         if ws_mccr_today.cell(i_sn, 35).value != 0:
#             str1 += "在库超365天数量=" + str(ws_mccr_today.cell(i_sn, 35).value) + "标准值=0\n"
#         else:
#             str1 += "在库超365天数量=0\n"
#     elif saterial_status == "正常B":
#         if ws_mccr_today.cell(i_sn, 21).value == 0:
#             str1 += "Y状态BOM成品引用次数=0，标准值≠0\n"
#         else:
#             str1 += "Y状态BOM成品引用次数≠0\n"
#     return str1


def change():  #
    lj_mc_change = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\mc_file\自定义报表数据变动.txt"
    f = open(lj_mc_change)  # 返回一个文件对象
    line = f.readline()  # 调用文件的 readline()方法
    list1 = []
    while line:
        if line == '\n':
            list1.append([])
        else:
            for i in ["\"", "\n", "变更人：", "变更前状态：", "变更后状态：", "变更意见：", "修改表格："]:
                if i in line: line = line.replace(i, "")
            list1[len(list1) - 1].append(line)
        line = f.readline()
    f.close()
    with open(lj_mc_change, "w") as file:
        file.write("")
    return list1


def changeDate_writeback(ws_change_ms, list_change):
    # wb = openpyxl.Workbook()
    # ws_change_ms = wb.worksheets[0]
    a = ws_change_ms.max_row + 1
    for i in list_change:
        list1 = vlookup(i[3], table_array_wlxxb, [2, 3])
        datetime1 = detect_date(i[1], 1)
        list2 = [i[3],  # 品号
                 list1[0],  # 品名
                 list1[1],  # 规格
                 datetime1,  # 变更时间
                 i[4],  # 变更前状态
                 i[5],  # 变更后状态
                 "人工调整",  # 变更类别
                 i[2],  # 判定人
                 datetime1,  # 判定时间
                 i[6]  # 判定意见
                 ]
        for j in range(0, len(list2)):
            ws_change_ms.cell(a, j + 1).value = list2[j]
        ws_change_ms.cell(a, 4).number_format = 'yyyy/mm/dd'
        ws_change_ms.cell(a, 9).number_format = 'yyyy/mm/dd'
        a += 1
